package ca.ualberta.cs.CLRDB;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

import ca.ualberta.cs.DB2ExceptionHandling.DB2SqlException;

import uk.ac.liv.c3connector.AnnotationModel;

public class HighlightsManagement extends EntityRelManagement {

	@Override
	public void initialize(Statement statement) throws SQLException {
		try{
			statement.executeUpdate("DROP TABLE Highlight");
	   }catch(Exception e){}
	
	   statement.executeUpdate("CREATE TABLE Highlight (" +
			    "ID INTEGER GENERATED ALWAYS AS " +
					"IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL, " +
				"personId INTEGER NOT NULL, " +  
				"resourceId INTEGER NOT NULL, " +
				"annotationBody CLOB(50K), " + 
				"CONSTRAINT FK_personid FOREIGN KEY (personId) " +
                	"REFERENCES Person (ID) ON DELETE CASCADE, " +
                "CONSTRAINT FK_resourceid FOREIGN KEY (resourceId) " +
                	"REFERENCES Resource (ID) ON DELETE CASCADE " +
			") IN CLRSPACE ");
		numOfCols = 4;
	}
	
	public int insertHighlight(AnnotationModel am, String username){
		int id = -1;
		
		Connection con = DatabaseController.connectToDB();
		
		try{
			Statement statement = con.createStatement();
			
			ResultSet resU = statement.executeQuery("SELECT ID FROM Person WHERE username = '" + username + "'");
			resU.next();
			Integer personId = resU.getInt("ID");
			
			Integer resourceId = -1;
			if(am.getResourceId() != null )
				resourceId = am.getResourceId();
			else{
				resourceId = ResourceManagement.resourceExists(am.resourceUri, con);
				if(resourceId == -1) //not exists
					resourceId = ResourceManagement.addResource(am.resourceUri,am.getDocumentDigest(),am.getDocumentTextDigest(),con);
			}
			
			if(resourceId != -1){
				PreparedStatement pstatement = con.prepareStatement("INSERT INTO Highlight (personId, resourceId, annotationBody) " +
						"VALUES (?,?,?)");
				
				
			}
			
			statement.close();
			con.commit();
		
		} catch (SQLException e) {
			DB2SqlException.printSqlErrorMsg(e);		
		} catch (Exception e) {			
			e.printStackTrace();			
		}
		return id;
	}

}
